package com.geotmt.billingcenter.modules.statis.mapper.provider;

import com.alibaba.druid.util.StringUtils;
import com.geotmt.billingcenter.common.utils.GeoDateUtils;
import com.geotmt.billingcenter.common.vo.params.ParamsConsume;
import com.geotmt.billingcenter.common.vo.params.ParamsCost;
import org.apache.ibatis.annotations.Param;

import java.util.Date;

/**
 * @Description 消耗的抽象类
 * @Author      yanghanwei
 * @Mail        yanghanwei@geotmt.com
 * @Date        2019/3/29 14:12
 * @Version     v1
 **/
public abstract class AbstractStatis {


    /************************************ consume ************************************/

    /**
     * preSql
     * @param obj
     */
    public String preSqlConsume(ParamsConsume obj,String alias){

        StringBuffer preSql = new StringBuffer();
        preSql.append(alias).append(".product_code,product.product_name,");
        preSql.append(alias).append(".company_id, com.company_full_name company_name,");
        preSql.append(alias).append(".cuser_id,cuser.real_name cuser_name,");
        preSql.append(alias).append(".interface_id,inter.interface_name,");
        preSql.append(alias).append(".isp,");
        String dayStr = this.formatDate(obj.getEffectStartTime(), obj.getEffectEndTime());
        return "'"+dayStr + "' day_flag,"+ preSql;
    }

    /**
     * 拼接 left join SQL
     * @param obj
     * @return
     */
    public String joinSqlConsume(ParamsConsume obj, String alias){
        StringBuffer preSql = new StringBuffer();
        preSql.append(" LEFT JOIN t_product product ON ").append(alias).append(".product_code=product.product_code ");
        preSql.append(" LEFT JOIN t_company_info com ON ").append(alias).append(".company_id=com.id ");
        preSql.append(" LEFT JOIN t_cuser_info cuser ON ").append(alias).append(".cuser_id=cuser.id ");
        preSql.append(" LEFT JOIN t_interface inter ON ").append(alias).append(".interface_id=inter.id ");
        return preSql.toString();
    }

    /**
     * after Sql
     * @param obj
     */
    public String groupSqlConsume(ParamsConsume obj,String alias){
        String group = " GROUP BY ";
        StringBuffer afterSql = new StringBuffer();
        StringBuffer sql = new StringBuffer();
        //产品
        if(StringUtils.isEmpty(obj.getProductCode())){
            afterSql.append(alias).append(".product_code,");
        }
        //客户
        if(null == obj.getCompanyId()){
            afterSql.append(alias).append(".company_id,");
        }
        //用户
        if(null == obj.getCuserId()){
            afterSql.append(alias).append(".cuser_id,");
        }
        //接口
        if(null == obj.getInterfaceId()){
            afterSql.append(alias).append(".interface_id,");
        }
        //运营商
        if(StringUtils.isEmpty(obj.getIsp())){
            afterSql.append(alias).append(".isp,");
        }
        if(!StringUtils.isEmpty(afterSql+"")){
            afterSql = new StringBuffer(afterSql.substring(0,afterSql.length()-1));
        }
        if(!StringUtils.isEmpty(afterSql)){
            sql.append(group).append(afterSql);
        }
        return sql.toString();
    }


    /**
     * 用户和接口条件
     * @param alias
     * @return
     */
    public String whereSqlConsume(@Param(value = "obj")ParamsConsume obj, String alias){
        StringBuffer sql = new StringBuffer();
        //产品
        if(!StringUtils.isEmpty(obj.getProductCode())){
            sql.append(" AND ").append(alias).append(".product_code=#{productCode}");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append(" AND ").append(alias).append(".company_id=#{companyId}");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append(" AND ").append(alias).append(".cuser_id=#{cuserId}");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append(" AND ").append(alias).append(".interface_id=#{interfaceId}");
        }
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append(" AND ").append(alias).append(".isp=#{isp}");
        }
        return sql.toString();
    }

    public String outerSelectConsume(@Param(value = "obj")ParamsConsume obj, String alias){
        StringBuffer sql = new StringBuffer();
        //产品
        if(!StringUtils.isEmpty(obj.getProductCode())) {
            sql.append(alias).append(".product_name productName,");
        } else {
            sql.append("'全部' productName,");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append(alias).append(".company_name companyName,");
        } else {
            sql.append("'全部' companyName,");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append(alias).append(".cuser_name cuserName,");
        } else {
            sql.append("'全部' cuserName,");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append(alias).append(".interface_name interface_name,");
        } else {
            sql.append("'全部' interfaceName,");
        }
        //运营商
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append(alias).append(".isp isp,");
        } else {
            sql.append("'全部' isp,");
        }
        return sql.toString();
    }


    public String innerSelectConsume(@Param(value = "obj")ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        if(!StringUtils.isEmpty(obj.getProductCode())) {
            sql.append("product.product_name product_name,");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append("com.company_full_name company_name,");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append("cuser.real_name cuser_name,");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append("inter.interface_name,");
        }
        //运营商
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append("con.isp isp,");
        }
        return sql.toString();
    }

    public String totalConsumeGroup(@Param(value = "obj")ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        if(!StringUtils.isEmpty(obj.getProductCode())) {
            sql.append("product.product_name,");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append("com.company_full_name,");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append("cuser.real_name,");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append("inter.interface_name,");
        }
        //运营商productName
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append("con.isp,");
        }
        if(!StringUtils.isEmpty(sql)){
            return new StringBuffer(" Group BY ").append(sql.substring(0,sql.length()-1)).toString();
        }
        return sql.toString();
    }

    /************************************ cost ************************************/
    /**
     * preSql
     * @param obj
     */
    public String preSqlCost(ParamsCost obj,String alias){
        StringBuffer preSql = new StringBuffer();
        preSql.append(alias).append(".product_code,product.product_name,");
        preSql.append(alias).append(".company_id, com.company_full_name company_name,");
        preSql.append(alias).append(".cuser_id,cuser.real_name cuser_name,");
        preSql.append(alias).append(".ds_id, ds.ds_full_name ds_name,");
        preSql.append(alias).append(".out_interface_id,out_inter.interface_name out_interface_name,");
        preSql.append(alias).append(".interface_id,inter.interface_name,");
        preSql.append(alias).append(".isp,");

        String dayStr = this.formatDate(obj.getEffectStartTime(), obj.getEffectEndTime());
        return dayStr + " day_flag,"+ preSql;
    }

    /**
     * 拼接 left join SQL
     * @param obj
     * @return
     */
    public String joinSqlCost(ParamsCost obj,String alias){
        StringBuffer preSql = new StringBuffer();
        preSql.append(" LEFT JOIN t_product product ON ").append(alias).append(".product_code=product.product_code ");
        preSql.append(" LEFT JOIN t_company_info com ON ").append(alias).append(".company_id=com.id ");
        preSql.append(" LEFT JOIN t_cuser_info cuser ON ").append(alias).append(".cuser_id=cuser.id ");
        preSql.append(" LEFT JOIN t_ds_info ds ON ").append(alias).append(".ds_id=ds.id ");
        preSql.append(" LEFT JOIN t_interface inter ON ").append(alias).append(".interface_id=inter.id ");
        preSql.append(" LEFT JOIN t_out_interface out_inter ON ").append(alias).append(".out_interface_id=out_inter.id ");
        return preSql.toString();
    }

    /**
     * after Sql
     * @param obj
     */
    public String groupSqlCost(ParamsCost obj,String alias){
        String group = " GROUP BY ";
        StringBuffer afterSql = new StringBuffer();
        StringBuffer sql = new StringBuffer();
        boolean flagGroup = false;
        //产品
        if(StringUtils.isEmpty(obj.getProductCode())){
            flagGroup = true;
            afterSql.append(alias).append(".product_code,");
        }
        //数据源
        if(null == obj.getDsId()){
            flagGroup = true;
            afterSql.append(alias).append(".ds_id,");
        }
        //客户
        if(null == obj.getCompanyId()){
            flagGroup = true;
            afterSql.append(alias).append(".company_id,");
        }
        //用户
        if(null == obj.getCuserId()){
            flagGroup = true;
            afterSql.append(alias).append(".cuser_id,");
        }
        //外部接口
        if(null == obj.getOutInterfaceId()){
            flagGroup = true;
            afterSql.append(alias).append(".out_interface_id,");
        }
        //接口
        if(null == obj.getInterfaceId()){
            flagGroup = true;
            afterSql.append(alias).append(".interface_id,");
        }
        //运营商
        if(StringUtils.isEmpty(obj.getIsp())){
            flagGroup = true;
            afterSql.append(alias).append(".isp,");
        }
        if(!StringUtils.isEmpty(afterSql+"")){
            afterSql = new StringBuffer(afterSql.substring(0,afterSql.length()-1));
        }
        if(!StringUtils.isEmpty(afterSql)){
            sql.append(group).append(afterSql);
        }
        return sql.toString();
    }

    /**
     * where 条件
     * @param effectStartTime
     * @param effectEndTime
     * @return
     */
    public String whereSql(Date effectStartTime, Date effectEndTime, String alias){
        StringBuffer sql = new StringBuffer();
        if(null != effectStartTime && null != effectEndTime){
            String startStr = GeoDateUtils.getDateStr(effectStartTime, GeoDateUtils.DATE_FORMAT2);
            String endStr = GeoDateUtils.getDateStr(effectEndTime, GeoDateUtils.DATE_FORMAT2);
            sql.append(" AND ").append(alias).append(".day_flag>=").append(startStr).append(" AND ").append(alias).append(".day_flag<=").append(endStr);
        }
        return sql.toString();
    }

    /**
     * 用户和接口条件
     * @param alias
     * @return
     */
    public String whereSqlCost(ParamsCost obj,String alias){
        StringBuffer sql = new StringBuffer();
        //产品
        if(!StringUtils.isEmpty(obj.getProductCode())){
            sql.append(" AND ").append(alias).append(".product_code=#{productCode}");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append(" AND ").append(alias).append(".company_id=#{companyId}");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append(" AND ").append(alias).append(".cuser_id=#{cuserId}");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append(" AND ").append(alias).append(".out_interface_id=#{outInterfaceId}");
        }
        return sql.toString();
    }

    public String outerSelectCost(@Param(value = "obj")ParamsCost obj, String alias){
        StringBuffer sql = new StringBuffer();
        //产品
        if(!StringUtils.isEmpty(obj.getProductCode())) {
            sql.append(alias).append(".product_name productName,");
        } else {
            sql.append("'全部' productName,");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append(alias).append(".company_name companyName,");
        } else {
            sql.append("'全部' companyName,");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append(alias).append(".cuser_name cuserName,");
        } else {
            sql.append("'全部' cuserName,");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append(alias).append(".interface_name interface_name,");
        } else {
            sql.append("'全部' interfaceName,");
        }
        //运营商
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append(alias).append(".isp isp,");
        } else {
            sql.append("'全部' isp,");
        }
        //数据源
        if(null != obj.getDsId()){
            sql.append(alias).append(".ds_name dsName,");
        } else {
            sql.append("'全部' dsName,");
        }
        //外部接口
        if(null != obj.getOutInterfaceId()){
            sql.append(alias).append(".out_interface_name outInterfaceName,");
        } else {
            sql.append("'全部' outInterfaceName,");
        }
        return sql.toString();
    }


    public String innerSelectCost(@Param(value = "obj")ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        if(!StringUtils.isEmpty(obj.getProductCode())) {
            sql.append("product.product_name product_name,");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append("com.company_full_name company_name,");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append("cuser.real_name cuser_name,");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append("inter.interface_name,");
        }
        //运营商
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append("cost.isp isp,");
        }
        //数据源
        if (null != obj.getDsId()){
            sql.append("ds.ds_full_name ds_name,");
        }
        //外部接口
        if (null != obj.getDsId()){
            sql.append("out_inter.interface_name out_interface_name,");
        }
        return sql.toString();
    }

    public String totalCostGroup(@Param(value = "obj")ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        if(!StringUtils.isEmpty(obj.getProductCode())) {
            sql.append("product.product_name,");
        }
        //客户
        if(null != obj.getCompanyId()){
            sql.append("com.company_full_name,");
        }
        //用户
        if(null != obj.getCuserId()){
            sql.append("cuser.real_name,");
        }
        //接口
        if(null != obj.getInterfaceId()){
            sql.append("inter.interface_name,");
        }
        //运营商productName
        if(!StringUtils.isEmpty(obj.getIsp())){
            sql.append("cost.isp,");
        }
        //数据源
        if(null != obj.getDsId()){
            sql.append("ds.ds_full_name,");
        }
        if(null != obj.getOutInterfaceId()){
            sql.append("out_inter.interface_name,");
        }
        if(!StringUtils.isEmpty(sql)){
            return new StringBuffer(" Group BY ").append(sql.substring(0,sql.length()-1)).toString();
        }
        return sql.toString();
    }

    /**
     * 处理时间的转化
     * @param effectStartTime
     * @param effectEndTime
     * @return
     */
    public String formatDate(Date effectStartTime, Date effectEndTime){
        String dayStr = "";
        if(null != effectStartTime && null != effectEndTime){
            String startStr = GeoDateUtils.getDateStr(effectStartTime, GeoDateUtils.DATE_FORMAT2);
            String endStr = GeoDateUtils.getDateStr(effectEndTime, GeoDateUtils.DATE_FORMAT2);
            if(startStr.equals(endStr)){
                dayStr = startStr;
            }else{
                dayStr = startStr + "-" + endStr;
            }
        }
        return dayStr;
    }
}
